﻿#include "dbdata.h"


extern MUser * puser1;
CDBdata::CDBdata()
{
    createConnection();
}
CDBdata::~CDBdata()
{
}
bool CDBdata::loadUser(QString vname)
{
    QSqlQuery q("SELECT * FROM muser where mname='"+vname+"'");
    QSqlRecord rec = q.record();
    qDebug() << "Number of columns: " << rec.count();
    int nameCol = rec.indexOf("mpassword"); // index of the field "name"
    int methodIdCol = rec.indexOf("methodid"); // index of the field "name"
    puser1->mname=vname;
    bool found=false;
    while (q.next())
    {
        puser1->password=q.value(nameCol).toString(); // output all names
        puser1->methodid=q.value(methodIdCol).toInt(); // output all names
        qDebug()<<puser1->password<<puser1->methodid;
        found=true;
    }
    qDebug()<<found;
    return found;
}
QString CDBdata::getLastUser()
{
    QSqlQuery q("SELECT * FROM program where id=1");
    QSqlRecord rec = q.record();
    qDebug() << "Number of columns: " << rec.count();
    int nameCol = rec.indexOf("lastUser"); // index of the field "name"
    QString r;
    while (q.next())
    {
        r=q.value(nameCol).toString(); // output all names
    }
    return r;
}
//void CDBdata::saveSig(CMySig sig1,long id)
//{
//}

//CMySig CDBdata::loadSig(long id)
//{
//    QString cmd;
//    QString idstr;
//    idstr.setNum(id);
//    cmd="SELECT * FROM voltnew where sampleid="+idstr;
//    qDebug()<<cmd;
//    QSqlQuery q(cmd);
//    QSqlRecord rec;// = q.record();
//    //qDebug() << "Number of columns: " << rec.count();
//    //,cvoltArr,svoltArr,c2voltArr,s2voltArr
//    CMySig sig1;
//    QString timestr;
//    while (q.next())
//    {
//        rec = q.record();
//        sig1.setXn(rec.value("xn").toInt());
//        sig1.setTimeArr(rec.value("mtimeArr").toByteArray());
//        sig1.setVoltArr(rec.value("cvoltArr").toByteArray(),0);
//        sig1.setVoltArr(rec.value("svoltArr").toByteArray(),1);
//        sig1.setVoltArr(rec.value("c2voltArr").toByteArray(),2);
//        sig1.setVoltArr(rec.value("s2voltArr").toByteArray(),3);
////        svolt=rec.value("svoltArr").toByteArray();
////        c2volt=rec.value("c2voltArr").toByteArray();
////        s2volt=rec.value("s2voltArr").toByteArray();
//    }
//    //sig1.print();
//    sig1.writeFile("test.txt");
//    return sig1;
//}

void CDBdata::c()
{
    QSqlRecord record;

    QSqlField f3("id", QVariant::Int);
    QSqlField f2("artist", QVariant::String);
    QSqlField f4("albumcount", QVariant::Int);

    f3.setValue(QVariant(1));
    f2.setValue(QVariant("hello"));
    f4.setValue(QVariant(1900));

    record.append(f2);
    record.append(f3);
    record.append(f4);
    QSqlTableModel* model = new QSqlTableModel(NULL);
    model->setTable("artists");
    model->insertRecord(-1, record);
}
static bool getAccessConnection(
        const QString& accessFile)
{
    //const QString PREFIX("puremilk.access.connection");

    //QString connID = PREFIX + "-" + accessFile;

    QSqlDatabase connection = QSqlDatabase::database();//connID, false);

    if (connection.isValid())
    {
        if (connection.isOpen())
            return true;
    }
    else
    {
        connection = QSqlDatabase::addDatabase("QSQLITE");//', connID);
    }
    // QString dsn = QString(
    //         "DRIVER={Microsoft Access Driver (*.mdb)};FIL={MS Access};DBQ=%1").arg(accessFile);

    // qDebug()<<"Get Access Database connection - "<<dsn;

    connection.setDatabaseName(accessFile);

    if (!connection.open())
    {
        //THROW_EXCEPTION(KDBException, connection.lastError().text());
        qDebug()<<"open failed";
        return false;
    }
    //        QSqlQuery query;
    //
    //        query.exec("create table artists (id int primary key, "
    //                                         "artist varchar(40), "
    //                                         "albumcount int)");
    //        query.exec("insert into artists values(0, '<all>', 0)");
    //        query.exec("insert into artists values(1, 'Ane Brun', 2)");
    //        query.exec("insert into artists values(2, 'Thomas Dybdahl', 3)");
    //        query.exec("insert into artists values(3, 'Kaizers Orchestra', 3)");
    //
    //        query.exec("create table albums (albumid int primary key, "
    //                                         "title varchar(50), "
    //                                         "artistid int, "
    //                                         "year int)");
    //
    //        query.exec("insert into albums values(1, 'Spending Time With Morgan', 1, "
    //                           "2003)");
    //        query.exec("insert into albums values(2, 'A Temporary Dive', 1, 2005)");
    //        query.exec("insert into albums values(3, '...The Great October Sound', 2, "
    //                           "2002)");
    //        query.exec("insert into albums values(4, 'Stray Dogs', 2, 2003)");
    //        query.exec("insert into albums values(5, "
    //            "'One day you`ll dance for me, New York City', 2, 2004)");
    //        query.exec("insert into albums values(6, 'Ompa Til Du D\xf8r', 3, 2001)");
    //        query.exec("insert into albums values(7, 'Evig Pint', 3, 2002)");
    //        query.exec("insert into albums values(8, 'Maestro', 3, 2005)");

    return true;
}
bool CDBdata::ctable()
{
    QSqlQuery query;

    query.exec("create table artists (id int primary key, "
               "artist varchar(40), "
               "albumcount int)");
    query.exec("insert into artists values(0, '<all>', 0)");
    query.exec("insert into artists values(1, 'Ane Brun', 2)");
    query.exec("insert into artists values(2, 'Thomas Dybdahl', 3)");
    query.exec("insert into artists values(3, 'Kaizers Orchestra', 3)");

    query.exec("create table albums (albumid int primary key, "
               "title varchar(50), "
               "artistid int, "
               "year int)");

    query.exec("insert into albums values(1, 'Spending Time With Morgan', 1, "
               "2003)");
    query.exec("insert into albums values(2, 'A Temporary Dive', 1, 2005)");
    query.exec("insert into albums values(3, '...The Great October Sound', 2, "
               "2002)");
    query.exec("insert into albums values(4, 'Stray Dogs', 2, 2003)");
    query.exec("insert into albums values(5, "
               "'One day you`ll dance for me, New York City', 2, 2004)");
    query.exec("insert into albums values(6, 'Ompa Til Du D\xf8r', 3, 2001)");
    query.exec("insert into albums values(7, 'Evig Pint', 3, 2002)");
    query.exec("insert into albums values(8, 'Maestro', 3, 2005)");
    return true;
}
bool CDBdata::createConnection()
{
    getAccessConnection("data.sqlite");
    qDebug()<<"get connection -----------------------";
    return true;
}
void CDBdata::r()
{
    QSqlQuery q("SELECT * FROM artists");
    QSqlRecord rec = q.record();

    qDebug() << "Number of columns: " << rec.count();

    int nameCol = rec.indexOf("artist"); // index of the field "name"
    while (q.next())
        qDebug() << q.value(nameCol).toString(); // output all names
}
void CDBdata::u()
{
    QSqlTableModel* model = new QSqlTableModel(NULL);
    model->setTable("artists");
    model->select();
    //    model->record(0).field(1).setValue("hello22");
    QSqlRecord record = model->record(0);
    record.setValue("artist", "new hello");
    model->setRecord(0, record);
    model->submitAll();
}
void CDBdata::d()
{
    QSqlTableModel* model = new QSqlTableModel(NULL);
    model->setTable("artists");
    model->select();
    model->removeRow(0);
    model->submitAll();
}
long CDBdata::nextSampleId()
{
    long n=0;
    QSqlTableModel* model = new QSqlTableModel(NULL);
    model->setTable("program");
    model->select();
    QSqlRecord record = model->record(0);
    n=record.value("sampleid").toInt();//取
    //增加1
    record.setValue("sampleid",QVariant((int)n+1));
    model->setRecord(0, record);
    model->submitAll();
    return  n + 1;
}
long CDBdata::nextId()
{
    long n=0;
    QSqlTableModel* model = new QSqlTableModel(NULL);
    model->setTable("program");
    model->select();
    QSqlRecord record = model->record(0);
    n=record.value("seqence_id").toInt();//取
    //增加1
    record.setValue("seqence_id",QVariant((int)n+1));
    model->setRecord(0, record);
    model->submitAll();
    return  n + 1;
}

void CDBdata::removeVoltNew(long id)
{
    QSqlQuery query;
    QString cmd;
    cmd.setNum(id);
    cmd="delete from voltnew where id="+cmd;
    qDebug()<<cmd;
    query.exec(cmd);

}
void CDBdata::insertTmpid(long id)
{
    QSqlQuery query;
    QString cmd;
    cmd.setNum(id);
    cmd="insert into tmpid(sampleid) values("+cmd+")";
    qDebug()<<cmd;
    query.exec(cmd);

}
void CDBdata::getChannels(QHash<QString, ChannelAD> * pgchannels)
{
    qDebug("getChannels====");
    QSqlQuery q("SELECT * FROM channel");

    //pgchannels->clear();
    while (q.next())
    {
        qDebug("got rows");
        ChannelAD a;
        QSqlRecord rec = q.record();
        a.channel=rec.value("channel").toInt();
        a.color=rec.value("color").toInt();
        a.ele=rec.value("ele").toString();
        a.enable=rec.value("enable").toBool();
        a.title=rec.value("title").toString();
        //pgchannels->append(a);
        (*pgchannels)[a.ele]=a;
    }
    qDebug()<<pgchannels;
}
void CDBdata::clearAll()
{
    QSqlQuery query;
    query.exec("delete  from tmpid");
    // QSqlQuery q("SELECT * FROM tmpid");

    //pgchannels->clear();
    // while (q.next())
    // {
    //     qDebug("got rows");
    //     ChannelAD a;
    //     QSqlRecord rec = q.record();
    //     qDebug()<<rec.value("sampleid").toInt();
    // }

}
